﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using CSharpDataAccess.Product;
using CSharpDataAccess.Factory;
using CSharpDataAccess;
using System.Data;

namespace CSharpDataAccessTest
{
    [TestClass]
    public class SqlServerTest
    {
        string stringConnection = "Data Source=25.10.226.166;Initial Catalog=Northwind;User ID=sa;Password=";
        DataProvider provider = DataProvider.SQLServer;

        [TestMethod]
        public void OpenConnectionTest()
        {
            //Arrange
            
            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            bool result = sql.Open();

            //Assert
            Assert.IsTrue(result);
        }

        [TestMethod]
        public void CloseConnectionTest()
        {
            //Arrange

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            sql.Open();
            bool result = sql.Close();

            //Assert
            Assert.IsTrue(result);
        }

        [TestMethod]
        public void ExecuteScalarTextTest()
        {
            //Arrange
            var query1 = @"INSERT INTO [csharpdataaccess].[dbo].[dummy_t]([varchartext]) VALUES ('Testing ExecuteNonQueryTextTest'); " + "SELECT CAST(scope_identity() AS int);";

            var query2 = "select max(id) from [csharpdataaccess].[dbo].[dummy_t]";

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            int result1 = sql.ExecuteScalar<int>(CommandType.Text, query1);
            int result2 = sql.ExecuteScalar<int>(CommandType.Text, query2);
            //Assert
            Assert.AreEqual<int>(result1, result2);
        }

        [TestMethod]
        public void ExecuteNonQueryTextTest()
        {
            //Arrange
            var query = @"INSERT INTO [Northwind].[dbo].[Categories] ([CategoryName] ,[Description],[Picture]) VALUES ('Test Category','Test Description')";

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            int result = sql.ExecuteNonQuery(CommandType.Text, query);

            //Assert
            Assert.AreEqual<int>(1, result);
        }      

        [TestMethod]
        public void ExecuteDataSetTextTest()
        {
            //Arrange
            var query = @"SELECT * FROM [Northwind].[dbo].[Customers]";

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            DataSet ds = sql.ExecuteDataSet(CommandType.Text, query);

            //Assert
            Assert.IsNotNull(ds);
            Assert.IsTrue(ds.Tables.Count > 0);

        }

        [TestMethod]
        public void ExecuteDataTableTextTest()
        {
            //Arrange
            var query = @"SELECT * FROM [Northwind].[dbo].[Customers]";

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            DataTable dt = sql.ExecuteDataTable(CommandType.Text, query);

            //Assert
            Assert.IsNotNull(dt);
            Assert.IsTrue(dt.Rows.Count > 0);

        }

        [TestMethod]
        public void ExecuteReaderTextTest()
        {
            //Arrange
            var query = @"SELECT * FROM [Northwind].[dbo].[Customers]";

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            var reader = sql.ExecuteDataReader<Customer>(CommandBehavior.CloseConnection,
                query,
                x => new Customer()
                {
                    CustomerID = (string)x["CustomerID"]
                });

            //Assert
            Assert.IsNotNull(reader);

            var customer = reader.First(x => x.CustomerID == "ALFKI");
            Assert.IsNotNull(customer);
            
        }

        [TestMethod]
        public void ExecuteReaderIDataRecordTextTest()
        {
            //Arrange
            var query = @"SELECT * FROM [Northwind].[dbo].[Customers]";

            IDataAccessHandlerFactory factory = new DataAccessHandlerFactory(stringConnection, provider);
            IDataAccessHandler sql = factory.CreateDataProvider();

            //Act
            var reader = sql.ExecuteDataReader(CommandBehavior.CloseConnection, query);

            //Assert
            Assert.IsNotNull(reader);

            var customer = reader.First(x => x.GetValue(x.GetOrdinal("CustomerID")).Equals("ALFKI"));
            Assert.IsNotNull(customer);

            var customers = (from IDataRecord r in reader
                           select new
                           {
                               CustomerID = (string)r["CustomerID"]                              
                           }).ToList();
            Assert.IsNotNull(customers);
            
            List<Customer> custs = new List<Customer>();
            foreach (var item in reader)
            {
                var cust = new Customer()
                {
                    CustomerID = item["CustomerID"].ToString()
                };

                Assert.IsNotNull(cust);
                custs.Add(cust);
            }
        }
    }
}
